package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modellayer.Company;
import modellayer.JobPosition;

/**
 * The JobPositionDB class to exchange data betwixt SQL database and java
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */

public class JobPositionDB {
	private Connection con;
	
	/**
	 * JobPositionDB Fields
	 * @param con
	 */
	
	
	
	/**
	 * Constructor for the JobPositionDB class, also creates a connection to the SQL Database
	 */
	public JobPositionDB() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	/**
	 * Search method which uses jobPositionId as parameter
	 * @param jobPositionId
	 * @return singleWhere(wClause)
	 */
	public JobPosition findJobPosition(int jobPositionId) {
		String wClause = "id="+jobPositionId;
		
		return singleWhere(wClause);
	}
	
	public ArrayList<JobPosition> getAllJobPositions() {
		return miscWhere("");
	}
	
	/**
	 * singleWhere method to execute a method in the database
	 * @param wClause
	 * @return jobPositionObj
	 */
	private JobPosition singleWhere(String wClause) {
		ResultSet results;
		JobPosition jobPositionObj = null;
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if ( results.next() )
			{
				jobPositionObj = buildJobPosition(results);
			}
			else
			{
				jobPositionObj = null;
			}
			
			stmt.close();
		}
		catch (Exception ex) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+ex);
		}
		
		return jobPositionObj;
	}
	
	private ArrayList<JobPosition> miscWhere(String wClause)
	{
		ArrayList<JobPosition> jpList = new ArrayList<JobPosition>();
		ResultSet results;
		
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while (results.next())
			{
				jpList.add(buildJobPosition(results));
			}
		}
		catch (Exception e)
		{
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+e);
		}
		
		return jpList;
	}

	/**
	 * A method which uses wClause parameters to construct a query, in a format that the SQL database understands
	 * @param wClause
	 * @return query
	 */
	private String buildQuery(String wClause) {
		String query = "SELECT id, name FROM sem2_JobPosition";
		
		if ( ! wClause.isEmpty() )
		{
			query += " WHERE "+wClause;
		}
		
		return query;
	}

	/**
	 * A method which extracts data from the database into Java
	 * @param results
	 * @return jobPositionObj
	 */
	private JobPosition buildJobPosition(ResultSet results) {
		JobPosition jobPositionObj = null;
		
		try {
			jobPositionObj = new JobPosition(results.getInt("id"), results.getString("name"));
		}
		catch (SQLException e) {
			System.out.println("Error building JobPosition object!\nError: " + e);
		}
		
		return jobPositionObj;
	}
}